/*
 * MIT License
 *
 * Copyright (c) 2023 北京凯特伟业科技有限公司
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */
package com.je.meta.rpc;

import com.alibaba.fastjson2.JSONObject;
import com.je.common.base.DynaBean;
import com.je.common.base.mapper.query.Query;
import com.je.common.base.service.MetaService;
import com.je.common.base.util.*;
import com.je.ibatis.extension.conditions.ConditionsWrapper;
import com.je.meta.model.dd.DictionaryItemVo;
import com.je.meta.rpc.dictionary.DictionaryRpcService;
import com.je.meta.service.DataImplService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.servicecomb.provider.pojo.RpcSchema;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.*;

/**
 * @program: jecloud-meta
 * @author: LIULJ
 * @create: 2021/8/10
 * @description:
 */
@RpcSchema(schemaId = "dataImplRpcService")
public class DataImplRpcServiceImpl implements DataImplRpcService {

    @Autowired
    private MetaService metaService;
    @Autowired
    private DictionaryRpcService dictionaryRpcService;
    @Autowired
    private DataImplService dataImplService;
    @Autowired
    private Environment environment;

    @Override
    public JSONObject generateTemplate(String id, List<DynaBean> datas, JSONObject returnObj) throws Exception {
        String folder = environment.getProperty("servicecomb.downloads.directory") + File.separator +  "template";
        DynaBean template = metaService.selectOneByPk("JE_SYS_DATATEMPLATE", id);
        Boolean haveTem = false;
        String filePath = "";
        String fileInfo = template.getStr("DATATEMPLATE_MBWJ", "");
        if (StringUtil.isNotEmpty(fileInfo)) {
            filePath = fileInfo.split("\\*")[1];
            if (FileOperate.existsFile(folder + filePath)) {
                haveTem = true;
            }
        }

        List<DynaBean> fields = metaService.select(ConditionsWrapper.builder().table("JE_SYS_DATAFIELD")
                .apply("JE_SYS_DATATEMPLATE_ID={0} AND DATAFIELD_HIDDEN!='1' ORDER BY SY_ORDERINDEX", id));
        String title = template.getStr("DATATEMPLATE_NAME");
        String expFloder = "/JE/data/upload/expdata-tem/" + DateUtils.formatDate(new Date(), "yyyyMM");
        FileOperate.createFolder(folder + "/JE/data/upload/expdata-tem/" + DateUtils.formatDate(new Date(), "yyyyMM"));
        String expPath = expFloder + "/" + template.getStr("DATATEMPLATE_NAME") + ".xls";
        boolean expData = false;
        if (returnObj.containsKey("title")) {
            title = returnObj.getString("title");
        }
        if (returnObj.containsKey("expPath")) {
            expPath = returnObj.getString("expPath");
        }
        if (returnObj.containsKey("expData")) {
            expData = returnObj.getBoolean("expData");
        }
        POIFSFileSystem fs = null;
        HSSFWorkbook wb = null;
        HSSFSheet sheet = null;
        if (haveTem) {
            fs = new POIFSFileSystem(new FileInputStream(folder + filePath));
            wb = new HSSFWorkbook(fs);
            sheet = wb.getSheetAt(0);
        } else {
            wb = new HSSFWorkbook();
            sheet = wb.createSheet("第一页");
        }
        List<DynaBean> dataColumns = new ArrayList<DynaBean>();
        if (!haveTem) {
            HSSFPalette customPalette = wb.getCustomPalette();
            //设置标题样式
            HSSFCellStyle titleStyle = wb.createCellStyle();
            HSSFFont titleFont = wb.createFont();
//            customPalette.setColorAtIndex(HSSFColor.LIGHT_GREEN.index, (byte) 219, (byte) 238, (byte) 243);
            titleFont.setFontName("宋体");
            //字号
            titleFont.setFontHeightInPoints((short) 18);
//			titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//            titleStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
//			titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            titleStyle.setLocked(true);
            titleStyle.setFont(titleFont);
            //设置列名样式
            HSSFCellStyle columnStyle = wb.createCellStyle();
            HSSFFont columnFont = wb.createFont();
            columnFont.setFontName("宋体");
            //字号
            columnFont.setFontHeightInPoints((short) 12);
//			columnFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            columnStyle.setAlignment(HorizontalAlignment.CENTER);
            columnStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            columnStyle.setFont(columnFont);
            columnStyle.setWrapText(true);
            columnStyle.setLocked(true);
            columnStyle.setBorderTop(BorderStyle.THIN);
            columnStyle.setBorderLeft(BorderStyle.THIN);
            columnStyle.setBorderRight(BorderStyle.THIN);
            columnStyle.setBorderBottom(BorderStyle.THIN);
            HSSFCellStyle smStyle = wb.createCellStyle();
            HSSFFont smFont = wb.createFont();
            smFont.setFontName("宋体");
            //字号
            smFont.setFontHeightInPoints((short) 8);
            smStyle.setFont(smFont);
            smStyle.setBorderTop(BorderStyle.THIN);
            smStyle.setBorderLeft(BorderStyle.THIN);
            smStyle.setBorderRight(BorderStyle.THIN);
            smStyle.setBorderBottom(BorderStyle.THIN);
            smStyle.setAlignment(HorizontalAlignment.CENTER);
            smStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            HSSFRow titleRow = sheet.createRow(0);
            titleRow.setHeightInPoints(30);
            //设置模版主键ID
            HSSFCell configCell = titleRow.createCell(0);
            configCell.setCellValue(id);
            //隐藏第一列
            sheet.setColumnHidden(0, true);
            //处理字段多表头数据
            Boolean haveMore = false;
            for (DynaBean moreColumn : fields) {
                if (!"morecolumn".equals(moreColumn.getStr("DATAFIELD_XTYPE")) && !StringUtil.isNotEmpty(moreColumn.getStr("DATAFIELD_SSDBT"))) {
                    dataColumns.add(moreColumn);
                    continue;
                }
                haveMore = true;
                String code = moreColumn.getStr("DATAFIELD_CODE");
                List<DynaBean> childrens = new ArrayList<DynaBean>();
                for (DynaBean dataColumn : fields) {
                    if (code.equals(dataColumn.getStr("DATAFIELD_SSDBT"))) {
                        childrens.add(dataColumn);
                        dataColumns.add(dataColumn);
                    }
                }
                moreColumn.set("children", childrens);
            }
            //创建标题
            HSSFCell titleCell = titleRow.createCell(1);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, dataColumns.size()));
            titleCell.setCellStyle(titleStyle);
            titleCell.setCellValue(title);
//			HSSFRegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, titleRangeAddress, sheet, wb);
//			HSSFRegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, titleRangeAddress, sheet, wb);
//			HSSFRegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, titleRangeAddress, sheet, wb);
//			HSSFRegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, titleRangeAddress, sheet, wb);
            //创建字段名
            HSSFRow moreColumnRow = sheet.createRow(1);
            HSSFRow columnRow = sheet.createRow(2);
            HSSFRow smRow = sheet.createRow(3);
            if (haveMore) {
                moreColumnRow.setHeightInPoints(18);
                columnRow.setHeightInPoints(18);
            } else {
                moreColumnRow.setHeightInPoints(11);
                columnRow.setHeightInPoints(11);
            }
            //设置标题
            Integer nowColumnIndex = 1;
            Set<String> columnCodes = new HashSet<String>();
            for (Integer i = 0; i < fields.size(); i++) {
                DynaBean moreColumn = fields.get(i);
                String columnCode = moreColumn.getStr("DATAFIELD_CODE");
                if (columnCodes.contains(columnCode)) {continue;}
                if ("morecolumn".equals(moreColumn.getStr("DATAFIELD_XTYPE"))) {
                    List<DynaBean> childrens = (List<DynaBean>) moreColumn.get("children");
                    if (childrens.size() <= 0) {
                        continue;
                    }
                    HSSFCell moreColumnCell = moreColumnRow.createCell(nowColumnIndex);
                    sheet.addMergedRegion(new CellRangeAddress(1, 1, nowColumnIndex, nowColumnIndex + childrens.size() - 1));
                    moreColumnCell.setCellStyle(columnStyle);
                    moreColumnCell.setCellValue(moreColumn.getStr("DATAFIELD_NAME"));
//					HSSFRegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, columnRangeAddress, sheet, wb);
//					HSSFRegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, columnRangeAddress, sheet, wb);
//					HSSFRegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, columnRangeAddress, sheet, wb);
//					HSSFRegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, columnRangeAddress, sheet, wb);
                    Integer moreWidth = moreColumn.getInt("DATAFIELD_WIDTH", 80);
                    //暂定比例  按照  1个字符是6像素计算
                    Double moreW = MathExtend.divide(moreWidth / (double) 6, 1, 0);
                    //将像素换算成excel宽度      此方法参数意思是：单位的1/256th字符宽度
                    sheet.setColumnWidth(nowColumnIndex, Integer.parseInt(new java.text.DecimalFormat("0").format(moreW)) * 256);
                    //处理多表头下的列
                    for (Integer j = 0; j < childrens.size(); j++) {
                        DynaBean column = childrens.get(j);
                        HSSFCell columnCell = columnRow.createCell(nowColumnIndex + j);
                        columnCell.setCellStyle(columnStyle);
                        columnCell.setCellValue(column.getStr("DATAFIELD_NAME"));
//						if(StringUtil.isNotEmpty(column.getStr("DATAFIELD_SM"))){
                        if ("1".equals(template.getStr("DATATEMPLATE_SM_CODE"))) {
                            HSSFCell smCell = smRow.createCell(nowColumnIndex + j);
                            smCell.setCellStyle(smStyle);
                            smCell.setCellValue(column.getStr("DATAFIELD_SM", ""));
                        }
//						}
                        Integer width = column.getInt("DATAFIELD_WIDTH", 80);
                        //暂定比例  按照  1个字符是6像素计算
                        Double w = MathExtend.divide(width / (float) 6, 1, 0);
                        //将像素换算成excel宽度      此方法参数意思是：单位的1/256th字符宽度
                        sheet.setColumnWidth(nowColumnIndex + j, Integer.parseInt(new java.text.DecimalFormat("0").format(w)) * 256);
                        columnCodes.add(column.getStr("DATAFIELD_CODE"));
                    }
                    nowColumnIndex += childrens.size();
                } else {
                    if (StringUtil.isNotEmpty(moreColumn.getStr("DATAFIELD_SSDBT"))) {continue;}
                    HSSFCell moreColumnCell = moreColumnRow.createCell(nowColumnIndex);
                    sheet.addMergedRegion(new CellRangeAddress(1, 2, nowColumnIndex, nowColumnIndex));
                    moreColumnCell.setCellStyle(columnStyle);
                    moreColumnCell.setCellValue(moreColumn.getStr("DATAFIELD_NAME"));
//					if(StringUtil.isNotEmpty(moreColumn.getStr("DATAFIELD_SM"))){
                    if ("1".equals(template.getStr("DATATEMPLATE_SM_CODE"))) {
                        HSSFCell smCell = smRow.createCell(nowColumnIndex);
                        smCell.setCellStyle(smStyle);
                        smCell.setCellValue(moreColumn.getStr("DATAFIELD_SM", ""));
                    }
//					}
                    CellRangeAddress columnRangeAddress = new CellRangeAddress(1, 2, nowColumnIndex, nowColumnIndex);
//					HSSFRegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, columnRangeAddress, sheet, wb);
//					HSSFRegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, columnRangeAddress, sheet, wb);
//					HSSFRegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, columnRangeAddress, sheet, wb);
//					HSSFRegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, columnRangeAddress, sheet, wb);
                    Integer moreWidth = moreColumn.getInt("DATAFIELD_WIDTH", 80);
                    //暂定比例  按照  1个字符是6像素计算
                    Double moreW = MathExtend.divide(moreWidth / 6, 1, 0);
                    //将像素换算成excel宽度      此方法参数意思是：单位的1/256th字符宽度
                    sheet.setColumnWidth(nowColumnIndex, Integer.parseInt(new java.text.DecimalFormat("0").format(moreW)) * 256);
                    nowColumnIndex++;
                }
                columnCodes.add(columnCode);
            }
            //设置标题
            for (Integer i = 1; i <= fields.size(); i++) {
                DynaBean column = fields.get(i - 1);
//				HSSFCell columnCell=columnRow.getCell(i);
//				columnCell.setCellStyle(columnStyle);
//				columnCell.setCellValue(column.getStr("DATAFIELD_NAME"));
                Integer width = column.getInt("DATAFIELD_WIDTH", 80);
                //暂定比例  按照  1个字符是6像素计算
                Double w = MathExtend.divide(width / 6, 1, 0);
                //将像素换算成excel宽度      此方法参数意思是：单位的1/256th字符宽度
                sheet.setColumnWidth(i, Integer.parseInt(new java.text.DecimalFormat("0").format(w)) * 256);
                //处理下拉框
                if ("1".equals(column.getStr("DATAFIELD_XLK"))) {
                    String configInfo = column.getStr("DATAFIELD_CONFIGINFO", "");
                    if (StringUtil.isNotEmpty(configInfo)) {
                        //获取字典数据
                        String ddCode = configInfo.split(ArrayUtils.SPLIT)[0];
                        List<DictionaryItemVo> dicItems = dictionaryRpcService.getDicList(ddCode, new Query(), false);
                        String[] dicNames = new String[dicItems.size()];
                        for (int j = 0; j < dicItems.size(); j++) {
                            dicNames[j] = dicItems.get(j).getText();
                        }
                        CellRangeAddressList regions = new CellRangeAddressList(2, 5000, i, i);
                        DVConstraint constraint = DVConstraint.createExplicitListConstraint(dicNames);
                        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
                        sheet.addValidationData(dataValidation);
                    }
                }
            }
        } else {
            for (DynaBean moreColumn : fields) {
                if (!"morecolumn".equals(moreColumn.getStr("DATAFIELD_XTYPE")) && !StringUtil.isNotEmpty(moreColumn.getStr("DATAFIELD_SSDBT"))) {
                    dataColumns.add(moreColumn);
                    continue;
                }
                String code = moreColumn.getStr("DATAFIELD_CODE");
                List<DynaBean> childrens = new ArrayList<DynaBean>();
                for (DynaBean dataColumn : fields) {
                    if (code.equals(dataColumn.getStr("DATAFIELD_SSDBT"))) {
                        childrens.add(dataColumn);
                        dataColumns.add(dataColumn);
                    }
                }
                moreColumn.set("children", childrens);
            }
        }
        //设置数据样式
        HSSFCellStyle dataStyle = wb.createCellStyle();
        HSSFFont dataFont = wb.createFont();
        dataFont.setFontName("宋体");
        //字号
        dataFont.setFontHeightInPoints((short) 10);
        dataStyle.setFont(dataFont);
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        int startRow = 4;
        if (expData) {
            //封装字典数据
            Map<String, Map<String, String>> ddInfos = buildDicDatas(fields);
            //创建数据
            for (Integer i = 0; i < datas.size(); i++) {
                HSSFRow dataRow = sheet.createRow(startRow + i);
                DynaBean dynaBean = datas.get(i);
                for (Integer j = 0; j < dataColumns.size(); j++) {
                    HSSFCell dataCell = dataRow.createCell(j + 1);
                    dataCell.setCellStyle(dataStyle);
                    DynaBean column = dataColumns.get(j);
                    if ("rownumberer".equals(column.getStr("DATAFIELD_XTYPE"))) {
                        dataCell.setCellValue(i + 1);
                        continue;
                    }
                    String xtype = column.getStr("DATAFIELD_XTYPE");
                    if (ArrayUtils.contains(new String[]{"rgroup", "cgroup", "cbbfield"}, xtype)) {
                        dataCell.setCellValue(dataImplService.getDdValue(ddInfos, column, dynaBean.getStr(column.getStr("DATAFIELD_CODE", ""))));
                    } else {
                        if (ArrayUtils.contains(new String[]{"numberfield"}, xtype)) {
                            dataCell.setCellValue(dynaBean.getDouble(column.getStr("DATAFIELD_CODE"), 0));
                        } else {
                            dataCell.setCellValue(dynaBean.getStr(column.getStr("DATAFIELD_CODE", "")));
                        }
                    }
                }
            }
        }
        if (haveTem) {
            FileOutputStream fileOut = new FileOutputStream(folder + expPath);
            returnObj.put("templatePath", expPath);
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } else {
            FileOutputStream fos = new FileOutputStream(folder + expPath);
            returnObj.put("templatePath", expPath);
//			sheet.createFreezePane(fields.size()+1, 3);
            wb.write(fos);
            fos.close();
        }
        return returnObj;
    }

    @Override
    public Map<String, Map<String, String>> buildDicDatas(List<DynaBean> columns) {
        Map<String, Map<String, String>> maps = new HashMap<String, Map<String, String>>();
        Set<String> ddCodes = new HashSet<String>();
        for (DynaBean column : columns) {
            String xtype = column.getStr("DATAFIELD_XTYPE");
            if (ArrayUtils.contains(new String[]{"rgroup", "cgroup", "cbbfield"}, xtype)) {
                String configInfo = column.getStr("DATAFIELD_CONFIGINFO");
                if (StringUtil.isNotEmpty(configInfo)) {
                    ddCodes.add(configInfo.split(ArrayUtils.SPLIT)[0]);
                }
            }
        }
        if (ddCodes.size() > 0) {
            List<DynaBean> dictionarys = metaService.select("JE_CORE_DICTIONARY",
                    ConditionsWrapper.builder().in("DICTIONARY_DDCODE", ddCodes)
                            .apply("and (SY_STATUS = '' or SY_STATUS = '1')"),
                    "JE_CORE_DICTIONARY_ID,DICTIONARY_DDCODE");
            for (DynaBean dictionary : dictionarys) {
                List<DynaBean> items = metaService.select("JE_CORE_DICTIONARYITEM", ConditionsWrapper.builder()
                                .apply("DICTIONARYITEM_DICTIONARY_ID={0} and SY_FLAG='1' and SY_NODETYPE != 'ROOT'", dictionary.getStr("JE_CORE_DICTIONARY_ID"))
                        , "JE_CORE_DICTIONARYITEM_ID,DICTIONARYITEM_ITEMCODE,DICTIONARYITEM_ITEMNAME");
                Map<String, String> ddItems = new HashMap<String, String>();
                for (DynaBean item : items) {
                    if (StringUtil.isNotEmpty(item.getStr("DICTIONARYITEM_ITEMCODE"))) {
                        ddItems.put(item.getStr("DICTIONARYITEM_ITEMCODE"), item.getStr("DICTIONARYITEM_ITEMNAME"));
                    }
                }
                maps.put(dictionary.getStr("DICTIONARY_DDCODE"), ddItems);
            }
        }
        return maps;
    }
}
